﻿/******************************************************************************
'Description:装试返件信息统计报表类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Zhunan
'create Date:2019.6.21
 ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace ReportBusiness
{
    public class ZSDealFaultBillBusiness
    {
        public uMESReportOracleDAL dal = new uMESReportOracleDAL();

        public DataTable GetAllFactory()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT f.factoryname,ua.attributevalue,f.factoryid FROM factory f");
            sb.AppendLine("LEFT JOIN Userattribute ua ON ua.parentid = f.factoryid AND ua.userattributename = '分厂代码'");
            sb.AppendLine("WHERE ua.attributevalue IS NOT NULL AND ua.attributevalue IN ('601','609','600','607','603')");
            return dal.GetDataTable(sb.ToString());
        }

        //查询--装试返件信息统计报表 add zhunan 2019.6.21
        public DataTable GetDealFaultBillInfoData(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            StringBuilder sb = new StringBuilder();
            string sql = @"
                    SELECT mfg.mfgordername,db.engineno,db.enginefamily,db.dealfaultbillname,db.productname,db.productbatchno,dbt.fromqty,
                           f.factoryname sendfactoryname,f2.factoryname tofactoryname,e6.fullname createemp,db.createdate,
                           DECODE(db.transferstatus,0,'',1,'',2,'',3,'返件返出',4,'返件接收',5,'返件完成',6,'返件返回',7,'已领回') status,
                           e.fullname FromEmp,dbt.FromTime,dbt.RequireFinishTime,e2.fullname RecvFactoryEmpname,dbt.RecvFactoryTime,
                           e3.fullname finishempname,dbt.finishtime,e4.fullname RecvFactoryReturnEmpname,dbt.recvFactoryReturnTime,
                           e5.fullname acceptteamname,dbt.AcceptTime
                    FROM DealFaultBill db
                    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
                    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = db.mfgorderid
                    LEFT JOIN Sessionvalues sv ON sv.employeeid = db.createemployeeid
                    LEFT JOIN factory f ON f.factoryid = sv.factoryid --申请分厂
                    LEFT JOIN factory f2 ON f2.factoryid = db.returnfactoryid --返件分厂
                    LEFT JOIN employee e ON e.employeeid = dbt.FromEmpID --返出人
                    LEFT JOIN employee e2 ON e2.employeeid = dbt.RecvFactoryEmpID --返件接收人
                    LEFT JOIN employee e3 ON e3.employeeid = dbt.finishempid --完成人
                    LEFT JOIN employee e4 ON e4.employeeid = dbt.RecvFactoryReturnEmpID --返回人
                    LEFT JOIN employee e5 ON e5.employeeid = dbt.acceptteamid--领取人
                    LEFT JOIN employee e6 ON e6.employeeid = db.createemployeeid
                    WHERE db.isuse = 0 AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df' ";
            
            sb.AppendLine(sql);
            //订单号
            if (para.ContainsKey("mfgordername"))
            {
                sb.AppendFormat(" AND mfg.mfgordername LIKE '%{0}%' ", para["mfgordername"]);
            }
            //发动机号
            if (para.ContainsKey("engineno"))
            {
                sb.AppendFormat(" AND db.engineno LIKE '%{0}%' ", para["engineno"]);
            }
            //机型
            if (para.ContainsKey("enginefamily"))
            {
                sb.AppendFormat(" AND db.enginefamily LIKE '%{0}%' ", para["enginefamily"]);
            }
            //排故单号
            if (para.ContainsKey("dealfaultbillname"))
            {
                sb.AppendFormat(" AND db.dealfaultbillname LIKE '%{0}%' ", para["dealfaultbillname"]);
            }
            //申请单位
            if (para.ContainsKey("sendfactoryname"))
            {
                sb.AppendFormat(" AND f.factoryname = '{0}' ", para["sendfactoryname"]);
            }
            //返件单位
            if (para.ContainsKey("tofactoryname"))
            {
                sb.AppendFormat(" AND f2.factoryname = '{0}' ", para["tofactoryname"]);
            }
            //状态
            if (para.ContainsKey("status"))
            {
                sb.AppendFormat(" AND db.transferstatus = '{0}' ", para["status"]);
            }

            //返出时间
            if (para.ContainsKey("FromTimeStart"))
            {
                sb.AppendFormat(" AND dbt.FromTime >= to_date('{0}','yyyy/MM/dd hh24:mi:ss')", para["FromTimeStart"]);
            }
            if (para.ContainsKey("FromTimeEnd"))
            {
                sb.AppendFormat(" AND dbt.FromTime <= to_date('{0}','yyyy/MM/dd hh24:mi:ss')", para["FromTimeEnd"]);
            }

            //返出时间
            if (para.ContainsKey("recvFactoryReturnTimeStart"))
            {
                sb.AppendFormat(" AND dbt.recvFactoryReturnTime >= to_date('{0}','yyyy/MM/dd hh24:mi:ss')", para["recvFactoryReturnTimeStart"]);
            }
            if (para.ContainsKey("recvFactoryReturnTimeEnd"))
            {
                sb.AppendFormat(" AND dbt.recvFactoryReturnTime <= to_date('{0}','yyyy/MM/dd hh24:mi:ss')", para["recvFactoryReturnTimeEnd"]);
            }

            //编制时间 add zhunan 2020.3.17
            if (para.ContainsKey("CreateDateStart"))
            {
                sb.AppendFormat(" AND db.createdate >= to_date('{0}','yyyy/MM/dd hh24:mi:ss')", para["CreateDateStart"]);
            }
            if (para.ContainsKey("CreateDateEnd"))
            {
                sb.AppendFormat(" AND db.createdate <= to_date('{0}','yyyy/MM/dd hh24:mi:ss')", para["CreateDateEnd"]);
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sb.ToString(), int.Parse(para["currentPage"].ToString()), int.Parse(para["pageSize"].ToString()));

            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);
            DataTable dtResult = retR.DBTable;
            
            return dtResult;
        }

        //导出--装试返件信息统计报表 add zhunan 2019.6.21
        public DataTable GetExportDealFaultBillInfoData(Dictionary<string, object> para)
        {
            StringBuilder sb = new StringBuilder();
            string sql = @"
                    SELECT mfg.mfgordername,db.engineno,db.enginefamily,db.dealfaultbillname,db.productname,db.productbatchno,dbt.fromqty,
                           f.factoryname sendfactoryname,f2.factoryname tofactoryname,e6.fullname createemp,db.createdate,
                           DECODE(db.transferstatus,0,'',1,'',2,'',3,'返件返出',4,'返件接收',5,'返件完成',6,'返件返回',7,'已领回') status,
                           e.fullname FromEmp,dbt.FromTime,dbt.RequireFinishTime,e2.fullname RecvFactoryEmpname,dbt.RecvFactoryTime,
                           e3.fullname finishempname,dbt.finishtime,e4.fullname RecvFactoryReturnEmpname,dbt.recvFactoryReturnTime,
                           e5.fullname acceptteamname,dbt.AcceptTime
                    FROM DealFaultBill db
                    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
                    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = db.mfgorderid
                    LEFT JOIN Sessionvalues sv ON sv.employeeid = db.createemployeeid
                    LEFT JOIN factory f ON f.factoryid = sv.factoryid --申请分厂
                    LEFT JOIN factory f2 ON f2.factoryid = db.returnfactoryid --返件分厂
                    LEFT JOIN employee e ON e.employeeid = dbt.FromEmpID --返出人
                    LEFT JOIN employee e2 ON e2.employeeid = dbt.RecvFactoryEmpID --返件接收人
                    LEFT JOIN employee e3 ON e3.employeeid = dbt.finishempid --完成人
                    LEFT JOIN employee e4 ON e4.employeeid = dbt.RecvFactoryReturnEmpID --返回人
                    LEFT JOIN employee e5 ON e5.employeeid = dbt.acceptteamid--领取人
                    LEFT JOIN employee e6 ON e6.employeeid = db.createemployeeid
                    WHERE db.isuse = 0 AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df' ";

            sb.AppendLine(sql);
            //订单号
            if (para.ContainsKey("mfgordername"))
            {
                sb.AppendFormat(" AND mfg.mfgordername LIKE '%{0}%' ", para["mfgordername"]);
            }
            //发动机号
            if (para.ContainsKey("engineno"))
            {
                sb.AppendFormat(" AND db.engineno LIKE '%{0}%' ", para["engineno"]);
            }
            //机型
            if (para.ContainsKey("enginefamily"))
            {
                sb.AppendFormat(" AND db.enginefamily LIKE '%{0}%' ", para["enginefamily"]);
            }
            //排故单号
            if (para.ContainsKey("dealfaultbillname"))
            {
                sb.AppendFormat(" AND db.dealfaultbillname LIKE '%{0}%' ", para["dealfaultbillname"]);
            }
            //申请单位
            if (para.ContainsKey("sendfactoryname"))
            {
                sb.AppendFormat(" AND f.factoryname = '{0}' ", para["sendfactoryname"]);
            }
            //返件单位
            if (para.ContainsKey("tofactoryname"))
            {
                sb.AppendFormat(" AND f2.factoryname = '{0}' ", para["tofactoryname"]);
            }
            //状态
            if (para.ContainsKey("status"))
            {
                sb.AppendFormat(" AND db.transferstatus = '{0}' ", para["status"]);
            }

            //返出时间
            if (para.ContainsKey("FromTimeStart"))
            {
                sb.AppendFormat(" AND dbt.FromTime >= to_date('{0}','yyyy/MM/dd hh24:mi:ss')", para["FromTimeStart"]);
            }
            if (para.ContainsKey("FromTimeEnd"))
            {
                sb.AppendFormat(" AND dbt.FromTime <= to_date('{0}','yyyy/MM/dd hh24:mi:ss')", para["FromTimeEnd"]);
            }

            //返出时间
            if (para.ContainsKey("recvFactoryReturnTimeStart"))
            {
                sb.AppendFormat(" AND dbt.recvFactoryReturnTime >= to_date('{0}','yyyy/MM/dd hh24:mi:ss')", para["recvFactoryReturnTimeStart"]);
            }
            if (para.ContainsKey("recvFactoryReturnTimeEnd"))
            {
                sb.AppendFormat(" AND dbt.recvFactoryReturnTime <= to_date('{0}','yyyy/MM/dd hh24:mi:ss')", para["recvFactoryReturnTimeEnd"]);
            }

            //编制时间 add zhunan 2020.3.17
            if (para.ContainsKey("CreateDateStart"))
            {
                sb.AppendFormat(" AND db.createdate >= to_date('{0}','yyyy/MM/dd hh24:mi:ss')", para["CreateDateStart"]);
            }
            if (para.ContainsKey("CreateDateEnd"))
            {
                sb.AppendFormat(" AND db.createdate <= to_date('{0}','yyyy/MM/dd hh24:mi:ss')", para["CreateDateEnd"]);
            }

            return dal.GetDataTable(sb.ToString());
        }

        //返件看板--本月返件数据统计（柱状图） add zhunan 2019.7.9
        public DataTable GetDealFaultBillCount()
        {
            //计算返件总数：
            //未返回 + 本月返回的数据
            string sql = @"
SELECT COUNT(1) total
FROM
(
    SELECT db.dealfaultbillname,dbt.recvFactoryReturnTime
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid 
    WHERE db.isuse = 0 
          AND db.transferstatus IN ('2','3','4','5')
          AND dbt.recvFactoryReturnTime IS NULL --未返回
          AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
                      
    UNION ALL 
                      
    SELECT db.dealfaultbillname,dbt.recvFactoryReturnTime
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid 
    WHERE db.isuse = 0 
          AND dbt.recvFactoryReturnTime IS NOT NULL --已返回
          AND dbt.recvFactoryReturnTime >= trunc(sysdate,'month') AND dbt.recvFactoryReturnTime <= trunc(last_day(sysdate)) + 1 --在本月返回
          AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'       
) t 
";
            DataTable dtTotal = dal.GetDataTable(sql);

            //计算已返出数量
            //未返回或本月返回的返件数据中有转出时间的数据
            sql = @"
SELECT COUNT(1) fanChuQty
FROM
(
    SELECT db.dealfaultbillname,dbt.recvFactoryReturnTime
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
    WHERE db.isuse = 0 
          AND dbt.recvFactoryReturnTime IS NULL --未返回 
          AND db.transferstatus IN ('2','3','4','5') 
          AND dbt.FromTime IS NOT NULL --返出时间不为空
          AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
                      
    UNION ALL 
                      
    SELECT db.dealfaultbillname,dbt.recvFactoryReturnTime
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid 
    WHERE db.isuse = 0 
         AND dbt.recvFactoryReturnTime IS NOT NULL --已返回
         AND dbt.recvFactoryReturnTime >= trunc(sysdate,'month') AND dbt.recvFactoryReturnTime <= trunc(last_day(sysdate)) + 1 --返回时间在本月
         AND dbt.FromTime IS NOT NULL
         AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
) t 
";

            DataTable dtFanChuQty = dal.GetDataTable(sql);

            //计算已完成数
            //未返回或本月返回的返件信息中有完成时间的信息条目
            sql = @"
SELECT COUNT(1) finishedQty
FROM
(
    SELECT db.dealfaultbillname,dbt.recvFactoryReturnTime
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid 
    WHERE db.isuse = 0 
          AND dbt.recvFactoryReturnTime IS NULL --未返回
          AND db.transferstatus IN ('2','3','4','5') 
          AND dbt.finishtime IS NOT NULL --完成时间为空
          AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
                      
    UNION ALL 
                      
    SELECT db.dealfaultbillname,dbt.recvFactoryReturnTime
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid 
    WHERE db.isuse = 0 
          AND dbt.recvFactoryReturnTime IS NOT NULL --已返回
          AND dbt.recvFactoryReturnTime >= trunc(sysdate,'month') AND dbt.recvFactoryReturnTime <= trunc(last_day(sysdate)) + 1 --在本月返回
          AND dbt.finishtime IS NOT NULL 
          AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
        
) t 
";
            DataTable dtFinishedQty = dal.GetDataTable(sql);

            //计算预超期数
            sql = @"
SELECT COUNT(1) yuChaoQiQty
FROM DealFaultBill db
LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
WHERE db.isuse = 0 AND dbt.estimatedcompletedate IS NOT NULL AND dbt.finishtime IS NULL 
      AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
      AND TRUNC(to_date(to_char(dbt.estimatedcompletedate,'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(SYSDATE,'yyyy/mm/dd'),'yyyy/mm/dd')) = 0
";

            DataTable dtYuChaoQiQty = dal.GetDataTable(sql);

            //计算超期数
            sql = @"
SELECT COUNT(1) chaoQiQty
FROM
(
    SELECT db.dealfaultbillid
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
    WHERE db.isuse = 0 AND dbt.estimatedcompletedate IS NOT NULL AND dbt.finishtime IS NOT NULL 
          AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
        AND TRUNC(to_date(to_char(dbt.finishtime,'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(dbt.estimatedcompletedate,'yyyy/mm/dd'),'yyyy/mm/dd')) > 0
                      
    UNION ALL
                      
    SELECT db.dealfaultbillid
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
    WHERE db.isuse = 0 AND dbt.estimatedcompletedate IS NOT NULL AND dbt.finishtime IS NULL
          AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
          AND TRUNC(to_date(to_char(SYSDATE,'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(dbt.estimatedcompletedate,'yyyy/mm/dd'),'yyyy/mm/dd')) > 0
)
";
            DataTable dtChaoQiQty = dal.GetDataTable(sql);

            sql = @" 
SELECT count(1) zuofeiQty 
FROM DealFaultBill db 
WHERE db.isuse = 2 
      --AND db.createdate >= trunc(sysdate,'month') AND db.createdate <= trunc(last_day(sysdate)) + 1
";
            DataTable dtZuoFeiQty = dal.GetDataTable(sql);

            DataTable dtResult = new DataTable();
            dtResult.Columns.Add("total");//总数
            dtResult.Columns.Add("fanChuQty");//已转出
            dtResult.Columns.Add("finishedQty");//已完成
            dtResult.Columns.Add("yuChaoQiQty");//预超期
            dtResult.Columns.Add("chaoQiQty");//超期
            dtResult.Columns.Add("zuofeiQty");//已作废

            DataRow dr = dtResult.NewRow();
            dr["total"] = dtTotal.Rows[0]["total"];
            dr["fanChuQty"] = dtFanChuQty.Rows[0]["fanChuQty"];
            dr["finishedQty"] = dtFinishedQty.Rows[0]["finishedQty"];
            dr["yuChaoQiQty"] = dtYuChaoQiQty.Rows[0]["yuChaoQiQty"];
            dr["chaoQiQty"] = dtChaoQiQty.Rows[0]["chaoQiQty"];
            dr["zuofeiQty"] = dtZuoFeiQty.Rows[0]["zuofeiQty"];

            dtResult.Rows.Add(dr);

            return dtResult;
        }

        //返件看板--本月各机型返件情况（饼图） add zhunan 2019.7.9
        public DataTable GetThisMonthDealFaultProductFamilyInfo()
        {
            string sql = @"
SELECT db.enginefamily,COUNT(1) qty
FROM DealFaultBill db
LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid 
WHERE db.isuse = 0 AND dbt.recvFactoryReturnTime IS NOT NULL
      AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
      AND dbt.recvFactoryReturnTime >= trunc(sysdate,'month') AND dbt.recvFactoryReturnTime <= trunc(last_day(sysdate)) + 1
GROUP BY db.enginefamily
";
            return dal.GetDataTable(sql);
        }

        //返件看板--本月各单位完成情况(柱状图) add zhunan 2019.7.9
        public DataTable GetZSThisMonthDealFaultBillFactoryCount()
        {
            //计算返件总数
            string sql = @"
SELECT t.factoryname,t.factoryid,COUNT(1) total
FROM
(
    SELECT f.factoryname,f.factoryid
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
    LEFT JOIN factory f ON f.factoryid = db.returnfactoryid
    WHERE db.isuse = 0 AND dbt.recvFactoryReturnTime IS NULL AND db.transferstatus < 6 
          AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
                      
    UNION ALL 
                      
    SELECT f.factoryname,f.factoryid
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
    LEFT JOIN factory f ON f.factoryid = db.returnfactoryid
    WHERE db.isuse = 0 AND dbt.recvFactoryReturnTime IS NOT NULL
          AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
          AND dbt.recvFactoryReturnTime >= trunc(sysdate,'month') AND dbt.recvFactoryReturnTime <= trunc(last_day(sysdate)) + 1
) t
GROUP BY t.factoryname,t.factoryid
";
            DataTable dtTotal = dal.GetDataTable(sql);

            //计算已返出数量
            sql = @"
SELECT t.factoryname,t.factoryid,COUNT(1) fanChuQty
FROM
(
    SELECT f.factoryname,f.factoryid
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
    LEFT JOIN factory f ON f.factoryid = db.returnfactoryid 
    WHERE db.isuse = 0 AND dbt.recvFactoryReturnTime IS NULL AND db.transferstatus < 6 AND dbt.FromTime IS NOT NULL
          AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
                      
    UNION ALL 
                      
    SELECT f.factoryname,f.factoryid
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
    LEFT JOIN factory f ON f.factoryid = db.returnfactoryid
    WHERE db.isuse = 0 AND dbt.recvFactoryReturnTime IS NOT NULL
          AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
        AND dbt.recvFactoryReturnTime >= trunc(sysdate,'month') AND dbt.recvFactoryReturnTime <= trunc(last_day(sysdate)) + 1
        AND dbt.FromTime IS NOT NULL
) t 
GROUP BY t.factoryname,t.factoryid
";

            DataTable dtFanChuQty = dal.GetDataTable(sql);

            //计算已完成数
            sql = @"
SELECT t.factoryname,t.factoryid,COUNT(1) finishedQty
FROM
(
    SELECT f.factoryname,f.factoryid
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
    LEFT JOIN factory f ON f.factoryid = db.returnfactoryid
    WHERE db.isuse = 0 AND dbt.recvFactoryReturnTime IS NULL AND db.transferstatus < 6 AND dbt.finishtime IS NOT NULL
          AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
                      
    UNION ALL 
                      
    SELECT f.factoryname,f.factoryid
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
    LEFT JOIN factory f ON f.factoryid = db.returnfactoryid 
    WHERE db.isuse = 0 AND dbt.recvFactoryReturnTime IS NOT NULL
        AND dbt.recvFactoryReturnTime >= trunc(sysdate,'month') AND dbt.recvFactoryReturnTime <= trunc(last_day(sysdate)) + 1
        AND dbt.finishtime IS NOT NULL
        AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
) t 
GROUP BY t.factoryname,t.factoryid
";
            DataTable dtFinishedQty = dal.GetDataTable(sql);

            //计算预超期数
            sql = @"
SELECT f.factoryname,f.factoryid,COUNT(1) yuChaoQiQty
FROM DealFaultBill db
LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
LEFT JOIN factory f ON f.factoryid = db.returnfactoryid
WHERE db.isuse = 0 AND dbt.estimatedcompletedate IS NOT NULL AND dbt.finishtime IS NULL
      AND TRUNC(to_date(to_char(dbt.estimatedcompletedate,'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(SYSDATE,'yyyy/mm/dd'),'yyyy/mm/dd')) = 0
      AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
GROUP BY f.factoryname,f.factoryid
";

            DataTable dtYuChaoQiQty = dal.GetDataTable(sql);

            //计算超期数
            sql = @"
SELECT t.factoryname,t.factoryid,COUNT(1) chaoQiQty
FROM
(
    SELECT f.factoryname,f.factoryid
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
    LEFT JOIN factory f ON f.factoryid = db.returnfactoryid
    WHERE db.isuse = 0 AND dbt.estimatedcompletedate IS NOT NULL AND dbt.finishtime IS NOT NULL
        AND TRUNC(to_date(to_char(dbt.finishtime,'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(dbt.estimatedcompletedate,'yyyy/mm/dd'),'yyyy/mm/dd')) > 0
        AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
             
    UNION ALL
                      
    SELECT f.factoryname,f.factoryid
    FROM DealFaultBill db
    LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
    LEFT JOIN factory f ON f.factoryid = db.returnfactoryid
    WHERE db.isuse = 0 AND dbt.estimatedcompletedate IS NOT NULL AND dbt.finishtime IS NULL
        AND TRUNC(to_date(to_char(SYSDATE,'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(dbt.estimatedcompletedate,'yyyy/mm/dd'),'yyyy/mm/dd')) > 0
        AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
) t
GROUP BY t.factoryname,t.factoryid
";
            DataTable dtChaoQiQty = dal.GetDataTable(sql);
            sql = @"
SELECT DISTINCT f.factoryname,f.factoryid
FROM DealFaultBill db
LEFT JOIN factory f ON f.factoryid = db.returnfactoryid
WHERE db.isuse = 0 AND f.factoryname IS NOT NULL AND f.factoryid NOT IN ('0004e280000026e5','0004e28000000dc9') --发动机总体事业部、无损检测中心
";
            DataTable dtResult = dal.GetDataTable(sql);
            dtResult.Columns.Add("total");//总数
            dtResult.Columns.Add("fanChuQty");//已转出
            dtResult.Columns.Add("finishedQty");//已完成
            dtResult.Columns.Add("yuChaoQiQty");//预超期
            dtResult.Columns.Add("chaoQiQty");//超期

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = "";
                //无损检测中心数据合并到检验检测事业部 add zhunan 2022.5.10
                if (dr["factoryid"].ToString() == "0004e280000032e8")
                {
                    filter = "factoryid = '0004e280000032e8' OR factoryid = '0004e28000000dc9'";
                    dr["total"] = dtTotal.Select(filter).Length > 0 ? dtTotal.Compute("Sum(total)", filter) : 0;
                    dr["fanChuQty"] = dtFanChuQty.Select(filter).Length > 0 ? dtFanChuQty.Compute("Sum(fanChuQty)", filter) : 0;
                    dr["finishedQty"] = dtFinishedQty.Select(filter).Length > 0 ? dtFinishedQty.Compute("Sum(finishedQty)", filter) : 0;
                    dr["yuChaoQiQty"] = dtYuChaoQiQty.Select(filter).Length > 0 ? dtYuChaoQiQty.Compute("Sum(yuChaoQiQty)", filter) : 0;
                    dr["chaoQiQty"] = dtChaoQiQty.Select(filter).Length > 0 ? dtChaoQiQty.Compute("Sum(chaoQiQty)", filter) : 0;
                }
                else
                {
                    filter = string.Format("factoryname = '{0}'", dr["factoryname"].ToString());
                    dr["total"] = dtTotal.Select(filter).Length > 0 ? dtTotal.Select(filter)[0]["total"] : 0;
                    dr["fanChuQty"] = dtFanChuQty.Select(filter).Length > 0 ? dtFanChuQty.Select(filter)[0]["fanChuQty"] : 0;
                    dr["finishedQty"] = dtFinishedQty.Select(filter).Length > 0 ? dtFinishedQty.Select(filter)[0]["finishedQty"] : 0;
                    dr["yuChaoQiQty"] = dtYuChaoQiQty.Select(filter).Length > 0 ? dtYuChaoQiQty.Select(filter)[0]["yuChaoQiQty"] : 0;
                    dr["chaoQiQty"] = dtChaoQiQty.Select(filter).Length > 0 ? dtChaoQiQty.Select(filter)[0]["chaoQiQty"] : 0;
                }
            }

            return dtResult;
        }

        //返件看板--返件信息(表格明细) add zhunan 2019.7.9
        public DataTable GetDealFaultBillDetail(Dictionary<string,object> para,out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT db.dealfaultbillname,db.engineno,db.productname,db.productbatchno,f.factoryname,
        CASE WHEN db.transferstatus = 2 AND dbt.revrejectreason IS NOT NULL THEN '接收退回'
            WHEN db.transferstatus = 5 AND dbt.revreturnrejectreason IS NOT NULL THEN '返回退回'
            WHEN db.transferstatus = 2 THEN '待返出'
            WHEN db.transferstatus = 3 THEN '待接收'
            WHEN db.transferstatus = 4 THEN '待完成'
            WHEN db.transferstatus = 5 THEN '待返回' 
        END status,
        dbt.FromTime,dbt.RequireFinishTime,dbt.RecvFactoryTime,dbt.estimatedcompletedate,dbt.finishtime,
        CASE WHEN dbt.estimatedcompletedate IS NOT NULL AND dbt.finishtime IS NOT NULL
                    THEN TRUNC(to_date(to_char(dbt.finishtime,'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(dbt.estimatedcompletedate,'yyyy/mm/dd'),'yyyy/mm/dd'))
            WHEN dbt.estimatedcompletedate IS NOT NULL AND dbt.finishtime IS NULL
                    THEN TRUNC(to_date(to_char(SYSDATE,'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(to_char(dbt.estimatedcompletedate,'yyyy/mm/dd'),'yyyy/mm/dd'))
        ELSE 0 END delayDays,db.faultdesc
FROM DealFaultBill db
LEFT JOIN dealfaultbilltransfer dbt ON dbt.dealfaultbillid = db.dealfaultbillid
LEFT JOIN factory f ON f.factoryid = db.returnfactoryid
WHERE db.isuse = 0 AND db.transferstatus IN ('2','3','4','5') AND db.createdate >= to_date('2022/4/6','yyyy/mm/dd') AND db.createemployeeid <> '00047480000079df'
";
            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["currentPage"].ToString()), int.Parse(para["pageSize"].ToString()));

            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            return retR.DBTable;   
        }
    }
}
